﻿SELECT TOP 10 REGION_U
,STATE
,WIRE_CENTER
,PROPERTY_ID_U
,NATIONAL_IND_U
,DWELLING_TYPE_U
,ADDRESS_U
,UNIT  AS UNIT_U
,CITY_NM_U
,REGION_DESC_U
,SEGMENT_U
,PROPERTY_NAME_U
,AM_U
,AE_ASSIGNED_U
,PORTFOLIO_NAME_U
,PR_STATUS_U
,C_STATUS_U
,MARKET_AREA_U
,DA_U
,WCDA_U
,EVENT_ID_U
,SONBR_U
,DISC_RSN_CD_U
,DISC_RSN_U   
,SALES_CODE_U
,DIST_CHANNEL_U
,BAN_U
,ORDER_TYPE_U
,SOURCE_CD_U
,POSTDATE_U
,PRODUCT_ID_U
,PRODUCT_NM_U       
,INWARD_OUTWARD_CD_U         
,PRODUCT_ACTION_CD_U  
,PROD_QTY_U
 FROM
(SELECT  
	 CASE  STATE 
	   WHEN  'IL'  THEN 'A'
	   WHEN  'IN'  THEN 'A'
	   WHEN  'MI'  THEN 'A'
	   WHEN  'WI'  THEN 'A'
	   WHEN  'OH'  THEN 'A'
	   WHEN  'CA'  THEN 'P'
	   WHEN  'NV'  THEN 'P'
	   WHEN  'CT'  THEN 'N'
	   WHEN  'TX'  THEN 'S'
	   WHEN  'KS'  THEN 'S'
	   WHEN  'AR'  THEN 'S'
	   WHEN  'OK'  THEN 'S'
	   WHEN  'MO'  THEN 'S'
	   ELSE 'U'
        END  AS  REGION_U
    ,STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
    ,WIRE_CENTER
	,BASE.PROPERTY_ID AS PROPERTY_ID_U
	,NATIONAL_IND_U
	,DWELLING_TYPE_U
	,ADDRESS  AS ADDRESS_U
	,UNIT
	,CITY_NM AS CITY_NM_U
	,REGION AS REGION_DESC_U
	,PROPERTY_NAME  AS PROPERTY_NAME_U
	,AM  AS AM_U
	,AE_ASSIGNED  AS AE_ASSIGNED_U
	,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
	,PR_STATUS  AS PR_STATUS_U
	,C_STATUS  AS C_STATUS_U
	,MARKET_AREA  AS MARKET_AREA_U
	,DISTRIBUTION_AREA AS DA_U
	,TRIM(WIRE_CENTER)||TRIM(DISTRIBUTION_AREA)  AS WCDA_U
	,BASE.EVENT_ID  AS EVENT_ID_U
	,SONBR  AS SONBR_U
	,DISC_RSN_CD_U
	,DISC_RSN_U   
	,BASE.SALES_CODE AS SALES_CODE_U
	,COALESCE(DIST_CHANNEL,SALES_CHANNEL_CD,'UNKNOWN')  AS DIST_CHANNEL_U
	,BAN AS BAN_U
	,ORDER_TYPE  AS ORDER_TYPE_U
	,SOURCE_CD  AS SOURCE_CD_U
	,POSTDATE  AS POSTDATE_U
	,BASE.PRODUCT_ID   AS PRODUCT_ID_U
	,PRODUCT_NM           AS PRODUCT_NM_U       
	,INWARD_OUTWARD_CD    AS INWARD_OUTWARD_CD_U         
	,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
    ,PROD_QTY AS PROD_QTY_U

	FROM
	(SELECT  PROPERTY_ID 
		,ADDRESS
		,UNIT
		,REGION
		,PROPERTY_NAME
		,NATIONAL_IND  AS  NATIONAL_IND_U
		,DWELLING_TYPE  AS DWELLING_TYPE_U
		,AM
		,AE_ASSIGNED
		,PORTFOLIO_NAME
		,PR_STATUS
		,C_STATUS
		,MARKET_AREA
		,DISTRIBUTION_AREA
		,CHK.EVENT_ID
		,SONBR
		,DISC_RSN_CD_U
		,DISC_RSN_U   
		,SALES_CODE
		,SALES_CHANNEL_CD
		,BAN
		,ORDER_TYPE
		,SOURCE_CD
		,POSTDATE
		,PRODUCT_ID   
		,INWARD_OUTWARD_CD             
		,PRODUCT_ACTION_CD             
		,PROD_QTY  
		,CITY_NM  
		,STATE   
		FROM
		(SELECT S.PROPERTY_ID 
		,S.ADDRESS
		,UNIT
		,SC.REGION
		,SC.PROPERTY_NAME
		,SC.NATIONAL_IND
		,SC.DWELLING_TYPE
		,SC.AM
		,SC.AE_ASSIGNED
		,SC.PORTFOLIO_NAME
		,SC.PR_STATUS
		,SC.C_STATUS
		,SC.MARKET_AREA
		,' ' AS DISTRIBUTION_AREA
		,E.RAAID
		,E.EVENT_ID
		,E.SONBR
		,DISC_RSN_CD_U
		,DISC_RSN_U   
		,SALES_CODE
		,SALES_CHANNEL_CD
		,BAN
		,ORDER_TYPE
		,SOURCE_CD
		,E.POSTDATE
		,E.CONTACT_NM
		,E.CITY_NM
		,E.STATE
		,E.ZIP_CD
		FROM 
		SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
		S
		,SMARTMOVES.TXSM101_SO_SCORE 	
		SC
  		,(SELECT RAAID
				,EVENT_ID
				,SONBR
				,DISC_RSN_CD_U
				,DISC_RSN_U   
				,SALES_CODE
				,SALES_CHANNEL_CD
				,BAN
				,ORDER_TYPE
				,SOURCE_CD
				,POSTDATE
				,CONTACT_NM
				,CITY_NM
				,ADDRESS
				,UNIT
				,STATE
				,ZIP_CD
				
				FROM
				(
				SELECT	REPOSITORY_AFFILIATE_ACCT_ID RAAID
				,EVENT_ID
				,SONBR
				,DISC_RSN_CD_U
				,DISC_RSN_U   
				,SALES_CODE
				,SALES_CHANNEL_CD
				,BAN
				,ORDER_TYPE
				,SOURCE_CD
				,POSTDATE
				,ADDRESS_ID
				,CONTACT_NM
				,CITY_NM
				,PRIMARY_ADDRESS_TXT
				,SECONDARY_ADDRESS_TXT AS  UNIT
				,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
				         ,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
				             THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
				                  TRIM(C.STREET_NM)                          
				             ELSE C.STREET_NM                                
				             END (NAMED ADR1)                                
				         ,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
				             THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
				             ELSE ADR1                                            
				             END (NAMED ADR2)                                     
				         ,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
				             THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
				             ELSE ADR2                                            
				             END (NAMED ADR3)                                     
				         ,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
				             THEN TRIM(ADR3) ||' '||                              
				                  TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
				             ELSE ADR3                                            
				             END (NAMED ADDRESS)      
					,C.ZIP_CD
					,C.TERRITORY_CD AS STATE
				 FROM  
			 	ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
				C  
				,
				(SELECT H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
				 		,H205.EVENT_ID
						,H205.SERVICE_ORDER_NBR        		SONBR
						,DISC_RSN_CD_U
						,DISC_RSN_U     
						,O.BAN
						,H205.EVENT_POSTED_DT    			POSTDATE
						,H205.EVENT_COMPLETION_DT    		CMPLDATE 
						,H205.EVENT_CLASS_CD				ORDER_TYPE
						,H205.DATA_SOURCE_CD				SOURCE_CD
						,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
						,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
						FROM
						 ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT H205
						 ,(SELECT O.ORDER_ID	
								,O.SERVICE_ORDER_CD
								,O.BAN
								,SALES_CHANNEL_CD
								,DISC_RSN_CD_U     
								,DISC_RSN_U
							  FROM	  TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
							  O					
							LEFT OUTER JOIN 
							(SELECT A.ORDER_ID
							,A.ORDER_ACTION_TYPE_CD
							,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
							,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
							FROM
							TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
							A 
							,TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
							C
							WHERE  A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
									AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
									AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
							        AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
							        AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') )D
							        ON O.ORDER_ID = D.ORDER_ID) 	O
												
						WHERE   H205.EVENT_POSTED_DT = DATE-4 
							AND H205.DATA_SOURCE_CD IN (150)
							AND O.ORDER_ID = SONBR
							AND RAAID > 0
						GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12   ) 
						S
					WHERE C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
                                                                                                AND C.ZIP_CD <> ' '
						AND C.ADDRESS_USAGE_CD = 'S' ) BASE
						GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17) 
						E
				WHERE TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
				AND S.ZIP = E.ZIP_CD
				AND S.PROPERTY_ID = SC.PROPERTY_ID
				AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) ) CHK

 LEFT OUTER JOIN 

(SELECT D.EVENT_ID
	,D.PRODUCT_ID                    
	,D.INWARD_OUTWARD_CD             
	,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
	,SUM(D.PRODUCT_QTY)    PROD_QTY                  
            
FROM ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  D
WHERE  D.DATA_SOURCE_CD IN (150)
AND D.INWARD_OUTWARD_CD  IN (1,4)
AND D.PRODUCT_ID > 0

GROUP BY 1,2,3,4) ACT

ON CHK.EVENT_ID = ACT.EVENT_ID) BASE

LEFT OUTER JOIN

(SELECT PRODUCT_ID
	,PRODUCT_NM
FROM
	ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
WHERE PRODUCT_LAST_EFFECTIVE_DT > DATE
AND PRODUCT_ID > 0
GROUP BY 1,2)  P
ON BASE.PRODUCT_ID = P.PRODUCT_ID

LEFT OUTER JOIN

(SELECT SALES_CODE
		,CHANNEL  AS DIST_CHANNEL
		FROM
		 MIS.SALES_CHANNEL_LOOKUP
		 GROUP BY 1,2) S
ON BASE.SALES_CODE = S.SALES_CODE

LEFT OUTER JOIN
/*   ADDED 9/9/09 PER MIST REQUEST  126821   */
(SELECT PROPERTY_ID
,WIRE_CENTER
FROM SMARTMOVES.TXSM011_SMOVES_PROPERTIES    
GROUP BY 1,2) W
ON BASE.PROPERTY_ID = W.PROPERTY_ID

WHERE PRODUCT_ACTION_CD IN ('I','O')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34) TOT

/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    */
LEFT OUTER JOIN

(SELECT WCDA
,SEGMENT AS SEGMENT_U
FROM SPECIAL_TABLES.DG8331_ABCD_SEGS
GROUP BY 1,2) SEG

ON TOT.WCDA_U = SEG.WCDA

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35;

/*  UVERSE ORDERS 
1/19/2010  CHANGED SPECIAL_TABLES.DG8331 TO 
SMARTMOVES.TXSM101_SO_SCORE AND DIST_CHANNEL SOURCE
TO CHANNEL FROM  CHANNEL_PARTNER   */
